﻿Imports Microsoft.Reporting.WinForms

Public Class clsReportMetricos

    Shared Function GetData(Project As Project.clsProject, ReportParmInfo As ReportParameterInfoCollection) As DataTable
        Const BIOL_Database = "DB_BIOL" 'assume that Project points to APOT database; need to also acquire data from biological database

        'retrieve parameters...

        Dim StaGrp As String = ""
        Dim Year As Integer = 0
        Try
            StaGrp = ReportParmInfo("StationGroup").Values(0)
            Year = ReportParmInfo("Year").Values(0)
        Catch ex As Exception
            Throw New MissingFieldException("Report definition does not contain StationGroup and/or Year parameters.")
        End Try

        Dim dt As New DataTable
        'note: the column names and types MUST match those of the table used to design the report
        'fields are: Indicador, Parametro, SortOrder (int), Meta, YearMo (int), Month, Value
        dt.Columns.Add("Indicador", GetType(String))
        dt.Columns.Add("Parametro", GetType(String))
        dt.Columns.Add("SortOrder", GetType(Integer))
        dt.Columns.Add("Meta", GetType(Double))
        dt.Columns.Add("YearMo", GetType(Integer))
        dt.Columns.Add("Month", GetType(String))
        dt.Columns.Add("Count", GetType(Integer))
        dt.Columns.Add("NumExceed", GetType(Integer))
        dt.Columns.Add("Value", GetType(Double))

        Dim lstRow As New List(Of clsRow)
        lstRow.Add(New clsRow(Year) With {.Indicador = "MINSA", .Parametro = "Cl Residual, pH, y Turbiedad   ", .SortOrder = 1, .PCodeGrp = "CLR_PH_TURB", .Meta = 0.8})
        lstRow.Add(New clsRow(Year) With {.Indicador = "MINSA", .Parametro = "Grupo III                      ", .SortOrder = 2, .PCodeGrp = "GRUPO III", .Meta = 1.0})
        lstRow.Add(New clsRow(Year) With {.Indicador = "MINSA", .Parametro = "Grupo IV                       ", .SortOrder = 3, .PCodeGrp = "GRUPO IV", .Meta = 1.0})
        lstRow.Add(New clsRow(Year) With {.Indicador = "MINSA", .Parametro = "Grupo V                        ", .SortOrder = 4, .PCodeGrp = "GRUPO V", .Meta = 1.0})
        lstRow.Add(New clsRow(Year) With {.Indicador = "MINSA", .Parametro = "Grupo VI                       ", .SortOrder = 5, .PCodeGrp = "GRUPO VI", .Meta = 1.0})
        lstRow.Add(New clsRow(Year) With {.Indicador = "MINSA", .Parametro = "Coliforme total                ", .SortOrder = 6, .PCodeGrp = "CTOTALES", .Meta = 0.95})
        lstRow.Add(New clsRow(Year) With {.Indicador = "MINSA", .Parametro = "Coliforme fecal                ", .SortOrder = 7, .PCodeGrp = "CFECALES", .Meta = 1.0})
        lstRow.Add(New clsRow(Year) With {.Indicador = "ASEP", .Parametro = "A-Car. Biológicas              ", .SortOrder = 1, .PCodeGrp = "ASEP_A", .Meta = 0.5})
        lstRow.Add(New clsRow(Year) With {.Indicador = "ASEP", .Parametro = "B-Car. Organolépticas y físicas", .SortOrder = 2, .PCodeGrp = "ASEP_B", .Meta = 0.25})
        lstRow.Add(New clsRow(Year) With {.Indicador = "ASEP", .Parametro = "C-Car. Químicas inorgáncas     ", .SortOrder = 3, .PCodeGrp = "ASEP_C", .Meta = 0.15})
        lstRow.Add(New clsRow(Year) With {.Indicador = "ASEP", .Parametro = "D-Car. Químicas orgánicas      ", .SortOrder = 4, .PCodeGrp = "ASEP_D", .Meta = 0.05})
        lstRow.Add(New clsRow(Year) With {.Indicador = "ASEP", .Parametro = "E-Car. Radiactivas             ", .SortOrder = 5, .PCodeGrp = "ASEP_E", .Meta = 0.05})

        'determine total number of values for each month and pcode group for specified year and station group (two parameters)

        'compile criteria so can properly evaluate non-detect values
        Dim criteria As New WRDB.Project.clsCriteria(Project, "Maestra")

        'use a dictionary to determine if entire column should be left blank (use first row as template for months)
        Dim dictHasData As New Dictionary(Of Integer, Boolean)
        For Each kv In lstRow(0).dictCols
            dictHasData.Add(kv.Key, False)
        Next

        'for each row, select data in pcode group
        For Each r As clsRow In lstRow

            'retrieve data for this row and add results to dictionaries associated with each column
            Dim MinCrit, MaxCrit As Double
            Dim PCodeGrp As String = r.PCodeGrp

            'select bulk of data from APOT master table; will store ALG in CCode field so can handle exceedance criteria differently (don't want to have to enter all those BIOL algae pcodes into APOT)
            Dim sql As String =
                $"SELECT Dta.Station_ID, Dta.Date_Time, Dta.PCode, Dta.RCode, Dta.Result, ''
                FROM Maestra Dta 
                INNER JOIN StaGrp ON Dta.Station_ID = StaGrp.Station_ID 
                INNER JOIN PCodeGrp ON Dta.PCode = PCodeGrp.PCode
                WHERE 
                    StaGrp.StationGroup = '{StaGrp}' AND
                    PCodeGrp.PCodeGroup = '{PCodeGrp}' AND
                    Dta.Date_Time BETWEEN {Project.DB.DateTime(New Date(Year, 10, 1))} AND {Project.DB.DateTime(New Date(Year + 1, 10, 1).AddSeconds(-1))}"

            If PCodeGrp = "ASEP_A" Or PCodeGrp = "GRUPO VI" Then 'must also retrieve algae from biological database; it must have an identical StationGroup assigned
                'note: the presence of *any* algae in effluent is a violation, but there are no entries if no algae found
                'therefore, will show 100% compliance if no records found in month (actually, uses this rule for all parameters and months)
                sql &= $"
                UNION
                SELECT Dta.Station_ID, Dta.Date_Time, Dta.PCode, Dta.RCode, Dta.Result, 'ALG'
                FROM {BIOL_Database}.DBO.Maestra Dta 
                INNER JOIN {BIOL_Database}.DBO.StaGrp StaGrp ON Dta.Station_ID = StaGrp.Station_ID 
                WHERE 
                    StaGrp.StationGroup = '{StaGrp}' AND
                    Dta.Date_Time BETWEEN {Project.DB.DateTime(New Date(Year, 10, 1))} AND {Project.DB.DateTime(New Date(Year + 1, 10, 1).AddSeconds(-1))}"
            End If
            Dim dr = Project.DB.ExecuteReader(Sql)
            While dr IsNot Nothing AndAlso dr.Read
                Dim Station_ID As String = TestNull(dr.GetValue(0), "")
                Dim Date_Time As Date = TestNull(dr.GetValue(1), DateTime.MinValue)
                Dim PCode As String = TestNull(dr.GetValue(2), "")
                Dim RCode As String = TestNull(dr.GetValue(3), "")
                Dim Result As Double = TestNull(dr.GetValue(4), Double.NaN)
                Dim CCode As String = TestNull(dr.GetValue(5), "")
                If Station_ID <> "" AndAlso Date_Time <> Date.MinValue AndAlso PCode <> "" AndAlso Not Double.IsNaN(Result) Then
                    Dim YearMo As Integer = Date_Time.Year * 100 + Date_Time.Month
                    r.dictCols(YearMo).Add(New Project.clsDataRecord(Station_ID, Date_Time, PCode, Result, CCode, RCode))
                End If
            End While
            dr.Close()

            'the list of values is assembled for all columns; determine the number of exceedances
            'by lumping all tests and exceedances for row and column together

            For Each kvCol In r.dictCols 'for each month column in the row total number of tests and exceedances (there will be 12)
                Dim YearMo = kvCol.Key
                Dim lstResults = kvCol.Value

                Dim sum As Double = 0.0 'these may be used to compute avg for FLR
                Dim num As Integer = 0

                Dim count As Integer = 0
                Dim numexceed As Integer = 0

                For Each kvResult In kvCol.Value
                    With kvResult
                        If criteria.TryGetCriteria(.StationID, .PCode, .DateTime, .RCode, .Result, MinCrit, MaxCrit) Then
                            If r.Indicador = "MINSA" AndAlso r.Parametro.ToUpper.StartsWith("GRUPO III") AndAlso .PCode = "FLR" Then
                                'note that FLR values for MINSA group 3 will be treated differently: because only a single monthly test
                                'is mandated, will compute average value of all tests and compare that to criteria, treating as a single sample
                                num += 1
                                sum += .Result
                            ElseIf .CCode = "ALG" Then
                                'any algae found in BIOL database is a violation
                                count += 1
                                numexceed += 1
                            Else
                                'accumulate all parameters when figuring compliance
                                count += 1
                                If .Result < MinCrit Or .Result > MaxCrit Then numexceed += 1
                            End If
                        End If
                    End With
                Next kvResult

                If num > 0 Then 'must be special case for FLR; add single entry using FLR average and exceedance
                    Dim avg As Double = sum / num
                    count += 1
                    If avg < MinCrit Or avg > MaxCrit Then numexceed += 1
                End If

                Dim value As Double = Nothing

                If r.Indicador = "MINSA" Then 'express as decimal
                    If count = 0 Then 'there are no tests, but of course no exceedances; treat like 100% compliance
                        value = 1.0
                    Else
                        value = 1.0 - numexceed / count
                    End If
                Else 'express as decimal times weight for row
                    If count = 0 Then
                        value = r.Meta * 1.0
                    Else
                        value = r.Meta * (1.0 - numexceed / count)
                    End If
                End If

                'add row to table for each list row and month combination
                dt.Rows.Add(r.Indicador, r.Parametro, r.SortOrder, r.Meta, YearMo, Month(YearMo), count, numexceed, value)

                If count > 0 Then dictHasData(kvCol.Key) = True
            Next kvCol
        Next r

        'if all cells in column have 0 count, reset value to nothing
        With lstRow(0) 'just use first entry to get 12 columns
            For Each kvCol In .dictCols
                If Not dictHasData(kvCol.Key) Then
                    For Each r In dt.Rows
                        If r("YearMo") = kvCol.Key Then
                            r("Value") = DBNull.Value
                            r("Count") = DBNull.Value
                            r("NumExceed") = DBNull.Value
                        End If
                    Next
                End If
            Next
        End With
        Return dt
    End Function

    Private Shared Function Month(YearMo As Integer) As String
        'format 201510 like "Oct 15"
        Return Choose(YearMo.ToString.Substring(4, 2), "Ene", "Feb", "Mar", "Abr", "May", "Jun", "Jul", "Ago", "Sep", "Oct", "Nov", "Dic") & " " & YearMo.ToString.Substring(2, 2)
    End Function

    Private Class clsRow
        Property Indicador As String
        Property Parametro As String
        Property SortOrder As Integer
        Property PCodeGrp As String
        Property Meta As String
        Property dictCols As New Dictionary(Of Integer, List(Of Project.clsDataRecord)) 'key is YearMo, list contains PCode, Result values for row and column; need PCode so can treat FLR differently

        Sub New(year As Integer)
            'add empty result list for each month column for this row
            Dim dt1 As Date = New Date(year, 10, 1)
            For i = 0 To 11
                Dim dt2 As Date = dt1.AddMonths(i)
                dictCols.Add(dt2.Year * 100 + dt2.Month, New List(Of Project.clsDataRecord))
            Next
        End Sub
    End Class

End Class

